"""
Case Type   : mysql protocol compatibility
Case Name   : 协议兼容下验证分区表结合二级分区
Create At   : 2024/10/23
Owner       : lvlintao666
Description :
    1、设置协议兼容相关配置
    2、协议兼容下建表
    3、分区表结合二级分区
    4、清理环境
Expect      :
    1、配置成功
    2、建表成功
    3、修改后查询
    4、清理成功
History     :
"""

import os
import time
import unittest

from yat.test import Node
from yat.test import macro
from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.CommonMySQL import CommonMySQL
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger

logger = Logger()


class IndeBlurredTestCase(unittest.TestCase):
    def setUp(self):
        logger.info(f'-----{os.path.basename(__file__)} start-----')
        self.constant = Constant()
        self.com = Common()
        self.comsh = CommonSH('PrimaryDbUser')
        self.pri_node = Node('PrimaryDbUser')
        self.my_node = Node('mysql5')
        self.db_name = "proto_test_db"
        self.conf_path = os.path.join(macro.DB_INSTANCE_PATH,
                                      'postgresql.conf')
        self.create_db = f"drop database if exists {self.db_name};" \
                         f"create database {self.db_name} dbcompatibility='B'"
        self.user = f"proto_test_15"
        self.password = f"proto_test123"
        self.create_u = f"drop user if exists {self.user} cascade;" \
                        f"create user {self.user} with password '{self.password}';" \
                        f"grant all privileges to {self.user};" \
                        f"select set_native_password('{self.user}','{self.password}','');"
        self.show_cmd = f"show enable_dolphin_proto;" \
                        f"show dolphin_server_port;"
        self.create_tb = f"drop table if exists tab_protocol_0015;" \
                       f"create table tab_protocol_0015( id tinyint, name varchar(100)) " \
                       f"partition by hash(id)( partition p1, partition p2, partition p3, partition p4); "
        self.alter = f"alter table tab_protocol_0015 rebuild partition p1; " \
                     f"alter table tab_protocol_0015 remove partitioning;" \
                     f"alter table tab_protocol_0015 check partition p1,p2;" \
                     f"alter table tab_protocol_0015 repair partition p1,p2;" \
                     f"alter table tab_protocol_0015 optimize partition p1,p2; "
        self.show_tab = f"show create table tab_protocol_0015;"
        self.flag = f"CREATE TABLE tab_protocol_0015 (\\n    id tinyint,\\n    name character varying(100)\\n)"
        self.clean_tb = f"drop table if exists tab_protocol_0015 cascade constraints;" \
                        f"drop user if exists {self.user} cascade;"
        self.clean_db = f"drop database {self.db_name};"

    def test_index(self):
        text = '--step1:设置协议兼容相关配置;expect:创建成功--'
        logger.info(text)
        msg = self.comsh.execute_gsguc('set',
                                       self.constant.GSGUC_SUCCESS_MSG,
                                       'enable_dolphin_proto = on')
        logger.info(msg)
        my_port = int(self.pri_node.db_port) + 10
        msg = self.comsh.execute_gsguc('set',
                                       self.constant.GSGUC_SUCCESS_MSG,
                                       f'dolphin_server_port = {my_port}')
        logger.info(msg)
        sed_cmd = f"sed -i '$a dolphin.default_database_name={self.db_name}' " \
                  f"{self.conf_path}"
        logger.info(sed_cmd)
        res = self.com.get_sh_result(self.pri_node, sed_cmd)
        logger.info(res)
        msg = self.comsh.restart_db_cluster()
        logger.info(msg)
        show_res = self.comsh.execut_db_sql(self.show_cmd)
        logger.info(show_res)
        self.assertIn('on', show_res, '执行失败' + text)
        self.assertIn(f'{my_port}', show_res, '执行失败' + text)

        logger.info(self.create_db)
        db_res = self.comsh.execut_db_sql(self.create_db)
        logger.info(db_res)
        self.assertIn(self.constant.CREATE_DATABASE_SUCCESS, db_res,
                      '执行失败' + text)
        logger.info(self.create_u)
        u_res = self.comsh.execut_db_sql(self.create_u, dbname=self.db_name)
        logger.info(u_res)
        self.assertIn(self.constant.CREATE_ROLE_SUCCESS_MSG, u_res,
                      '执行失败' + text)

        text = '--step2:M*连接数据库建表建索引;expect:创建成功--'
        logger.info(text)
        my_sh = CommonMySQL('mysql5')
        c_res = my_sh.execut_db_sql(self.create_tb, self.db_name, self.pri_node.db_host,
                                    my_port, self.user, self.password)
        logger.info(c_res)
        self.assertNotIn('ERROR', c_res, '执行失败' + text)

        text = '--step3:M*连接数据库修改索引;expect:成功--'
        logger.info(text)
        al_res = my_sh.execut_db_sql(self.alter, self.db_name, host=self.pri_node.db_host,
                                     port=my_port, user=self.user, password=self.password)
        logger.info(al_res)
        self.assertNotIn('ERROR', al_res, '执行失败' + text)

        show_res = my_sh.execut_db_sql(sql=self.show_tab, dbname=self.db_name, host=self.pri_node.db_host,
                                       port=my_port, user=self.user, password=self.password)
        logger.info(show_res)
        self.assertNotIn('ERROR', show_res, '执行失败' + text)
        self.assertIn(self.flag, show_res, '执行失败' + text)

    def tearDown(self):
        text = '--step4:清理环境;expect:成功--'
        logger.info(text)
        clean_res = self.comsh.execut_db_sql(self.clean_tb, dbname=self.db_name)
        logger.info(clean_res)
        self.assertIn(self.constant.DROP_TABLE_SUCCESS, clean_res,
                      '执行失败' + text)
        self.assertIn(self.constant.DROP_ROLE_SUCCESS_MSG, clean_res,
                      '执行失败' + text)
        clean_res = self.comsh.execut_db_sql(self.clean_db)
        logger.info(clean_res)
        self.assertIn(self.constant.DROP_DATABASE_SUCCESS, clean_res,
                      '执行失败' + text)
        msg = self.comsh.execute_gsguc('set',
                                       self.constant.GSGUC_SUCCESS_MSG,
                                       'enable_dolphin_proto = off')
        logger.info(msg)
        self.assertTrue(msg, '执行失败' + text)
        msg = self.comsh.execute_gsguc('set',
                                       self.constant.GSGUC_SUCCESS_MSG,
                                       f'dolphin_server_port = 3308')
        logger.info(msg)
        self.assertTrue(msg, '执行失败' + text)
        sed_cmd = f"sed -i '/dolphin.default_database_name={self.db_name}/d' " \
                  f"{self.conf_path}"
        logger.info(sed_cmd)
        res = self.com.get_sh_result(self.pri_node, sed_cmd)
        logger.info(res)
        msg = self.comsh.restart_db_cluster()
        logger.info(msg)
        status = self.comsh.get_db_cluster_status()
        logger.info(status)
        self.assertTrue("Degraded" in status or "Normal" in status)
        logger.info(f'-----{os.path.basename(__file__)} end-----')
